package com.hnkywl.sts.action.zjgl;

import com.hnkywl.sts.entity.base.Sjzdz;
import com.hnkywl.sts.entity.sys.Gncz;
import com.hnkywl.sts.entity.zjgl.Pj;
import com.hnkywl.sts.service.organ.KhhService;
import com.hnkywl.sts.service.organ.NbgsService;
import com.hnkywl.sts.service.sys.GnczService;
import com.hnkywl.sts.service.zjgl.PjglService;
import com.hnkywl.sts.util.CalcUtils;
import com.hnkywl.sts.util.POIUtil;
import net.ruixin.web.struts2.SimpleActionSupport;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.springframework.beans.factory.annotation.Autowired;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

/**
 * 说明：
 * 包:        com.hnkywl.sts.action.sfgl
 * 类描述:    岗位Action类
 * 修改备注: [说明本次修改内容]
 * 版本:        [v1.0]
 */
@Namespace("/zjgl/pjgl")
public class PjglAction extends SimpleActionSupport {
    private PjglService pjglService;
    //票据实例
    private Pj pj;
    //票据兑换实例
    private Pj pjdh;
    //票据Id
    private Long pjId;
    //分页组件 起始位置
    private int page;
    //分页组件 页面大小
    private int rows = 10;
    //远程ajax验证的值
    private String validateVal;
    //需要远程ajax验证的属性
    private String validateField;
    //票据收付类型 应收票据 应付票据
    private Integer pjsflxType = 0;
    //票据类型 银票 商票
    private Integer  pjlxType;
    //考核状态  未考核  已考核
    private Integer khztType;
    //公司ID
    private Long gsId;
    //公司名称
    private String gsMc;
    //开户行Id
    private Long khhId;
    //开户行名称
    private String khhMc;
   //批量上传的pjId
    private String pjIds;
    private  List<Pj> pjList = new ArrayList<Pj>();
    private NbgsService nbgsService;
    //票据开始时间
    private Date pjStartDate;
    //票据结束时间
    private Date pjEndDate;

    //贴现需要的参数
    private Long yhmcId;
    private String yhmc;
    private Long gsmcId;
    private String gsmc;
    private Long bmmcId;
    private Long sqrId;
    private Sjzdz lxfylb;

    //贴现利息金额
    private String txlxzc;

    //承兑利息金额
    private String cdlxzc;


    private KhhService khhService;
    private Long sfkId;

    //功能操作
    private List<Gncz> gnczList;
    private GnczService gnczService;

    private String pjhm;

    private String sysDate;

    private Integer zt;

    private String bz;


    //票据查询分页显示
    @Action("pjglList")
    public void pjglList() {
        if (pjStartDate != null)
            pjStartDate = convertDate(pjStartDate, true);
        if (pjEndDate != null)
            pjEndDate = convertDate(pjEndDate, false);
        this.renderJson(this.pjglService.findAllPjgl(pjhm,sfkId,pjsflxType, pjlxType, khztType, gsId,gsMc,khhMc,pjStartDate,pjEndDate,zt,page, rows));
    }
    //票据兑换明细分页查询
    @Action("find_pjdhList")
    public void findPjdhList() {
        this.renderJson(this.pjglService.findPjdhmx(pjId,page, rows));
    }

    //票据管理未使用
    @Action("pjglwsyList")
    public void ygList() {
        this.renderJson(this.pjglService.findPjglWsy(gsId,pjsflxType,khhMc,pjhm, page, rows));
    }

    @Action("pjgl")
    public String pjgl(){
        SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd");
        sysDate = simple.format(new Date());
        gnczList = gnczService.findGnczByJsAndUrl(request.getServletPath());
        return SUCCESS;
    }
    //根据Ids返回查询结果
    @Action("pjList")
    public void pjList() {
        String[] ids = pjIds.split(",");
        for(String id :ids){
            Pj j = pjglService.findPjDetail(Long.parseLong(id));
            pjList.add(j);
        }
        this.renderJson(this.pjglService.findPjgl( pjList));
    }

    @Action("pjgl_content")
    public String pjglContent() {
        //进入update页面
        if (pjId != null) {
            pj = pjglService.findPjDetail(pjId);
        } else{

        }
        return SUCCESS;
    }
    //进入票据兑换页面
    @Action("pjgl_pjdh")
    public String pjglPjdh() {
        //进入票据兑换
        pj = pjglService.findPjDetail(pjId);
        return SUCCESS;
    }
    //进入票据兑换明细页面
    @Action("pjgl_pjdh_detail")
    public String pjglPjdhDetail() {
        return SUCCESS;
    }


    //票据兑换
    @Action("pjdh_pjgl")
    public void pjdhPjgl() {
        try {
            this.pjglService.pjdh(pjId,pjdh, 1);
            renderText("票据兑换成功");
        } catch (Exception e) {
            e.printStackTrace();
            renderText(e.getMessage().toString());
        }
    }

    /**
     * 方法名:     addPjgl
     * 功能描述:      增加票据
     * 输入参数:
     * 异常:      <RuntimeException  Exception>
     * 创建人:     zsp
     * 创建日期:   13-3-29上午9:47
     * 修改人:     zsp
     * 修改日期:   13-3-29上午9:47
     */
    @Action("add_pjgl")
    public void addPjgl() {
        try {
            this.pjglService.savePjgl(pj,1);
            renderText("{\"statusCode\":\"200\", \"message\":\"添加应付票据成功！\", \"operationType\":\"add\"}");
        } catch (Exception e) {
            e.printStackTrace();
            renderText("{\"statusCode\":\"300\", \"message\":\"" + e.getMessage() + "\"}");
        }
    }

    /**
     * 方法名:     updatePjgl
     * 功能描述:      修改票据
     * 输入参数:
     * 异常:      <RuntimeException  Exception>
     * 创建人:     scl
     * 创建日期:   13-3-29上午11:50
     * 修改人:     scl
     * 修改日期:   13-3-29上午11:50
     */
    @Action("update_pjgl")
    public void updatePjgl() {
        try {
            this.pjglService.updatePjgl(pj);
            renderText("{\"statusCode\":\"200\",\"message\":\"修改票据成功！\", \"operationType\":\"update\"}");
        } catch (RuntimeException e) {
            e.printStackTrace();
            renderText("{\"statusCode\":\"300\", \"message\":\"" + e.getMessage() + "\"}");
        }
    }

    /**
     * 方法名:     deletePjgl
     * 功能描述:      删除票据
     * 输入参数:
     * 异常:      <RuntimeException  Exception>
     * 创建人:     zsp
     * 创建日期:   13-3-29上午11:47
     * 修改人:     zsp
     * 修改日期:   13-3-29上午11:47
     */
    @Action("delete_pjgl")
    public void deletePjgl() {
        try {
            this.pjglService.deletePjgl(pjId);
            renderText("{\"statusCode\":\"200\", \"message\":\"删除票据成功！\", \"operationType\":\"delete\"}");
        } catch (Exception e) {
            e.printStackTrace();
            renderText("{\"statusCode\":\"300\", \"message\":\"" + e.getMessage() + "\"}");
        }
    }

    /**
     * 方法名:     pjglPjtx
     * 功能描述:     展现票据贴现页面
     * 输入参数:
     * 异常:      <RuntimeException  Exception>
     * 创建人:     zsp
     * 创建日期:   13-3-29上午11:47
     * 修改人:     zsp
     * 修改日期:   13-3-29上午11:47
     */
    @Action("pjgl_pjtx")
    public String pjglPjtx() {
        String[] ids = pjIds.split(",");
        Pj j = pjglService.findPjDetail(Long.parseLong(ids[0]));
        gsmcId = j.getNbgs().getId();
        gsmc = j.getNbgs().getJc();
        return SUCCESS;
    }


    /**
     * 方法名:     pjglPjts
     * 功能描述:     展现票据托收页面
     * 输入参数:
     * 异常:      <RuntimeException  Exception>
     * 创建人:     zsp
     * 创建日期:   13-3-29上午11:47
     * 修改人:     zsp
     * 修改日期:   13-3-29上午11:47
     */
    @Action("pjgl_pjts")
    public String pjglPjts() {
        String[] ids = pjIds.split(",");
        Pj j = pjglService.findPjDetail(Long.parseLong(ids[0]));
        gsmcId = j.getNbgs().getId();
        gsmc = j.getNbgs().getJc();
        return SUCCESS;
    }

    /**
     * 方法名:     pjglPjcd
     * 功能描述:     展现票据承兑页面
     * 输入参数:
     * 异常:      <RuntimeException  Exception>
     * 创建人:     zsp
     * 创建日期:   13-3-29上午11:47
     * 修改人:     zsp
     * 修改日期:   13-3-29上午11:47
     */
    @Action("pjgl_pjcd")
    public String pjglPjcd() {
        String[] ids = pjIds.split(",");
        Pj j = pjglService.findPjDetail(Long.parseLong(ids[0]));
        gsmcId = j.getNbgs().getId();
        gsmc = j.getNbgs().getJc();
        yhmcId = j.getYfcpyh().getId();
        yhmc = j.getYfcpyh().getQc();
        return SUCCESS;
    }

    /**
     * 票据贴现处理
     * @return
     */
    @Action("pjtx_pjgl")
    public void pjtxPjgl() {
        try {
            this.pjglService.Pjtx(pjIds,txlxzc,sqrId,yhmcId,bz);
            renderText("{\"statusCode\":\"200\", \"message\":\"票据贴现成功！\", \"operationType\":\"add\"}");
        } catch (Exception e) {
            e.printStackTrace();
            renderText("{\"statusCode\":\"300\", \"message\":\"" + e.getMessage() + "\"}");
        }
    }

    /**
     * 票据托收处理
     * @return
     */
    @Action("pjts_pjgl")
    public void pjtsPjgl() {
        try {
            this.pjglService.Pjts(pjIds, sqrId,yhmcId,bz);
            renderText("{\"statusCode\":\"200\", \"message\":\"票据托收成功！\", \"operationType\":\"add\"}");
        } catch (Exception e) {
            e.printStackTrace();
            renderText("{\"statusCode\":\"300\", \"message\":\"" + e.getMessage() + "\"}");
        }
    }

    /**
     * 票据承兑处理
     * @return
     */
    @Action("pjcd_pjgl")
    public void pjcdPjgl() {
        try {
            this.pjglService.Pjcd(pjIds, sqrId,yhmcId,bz);
            renderText("{\"statusCode\":\"200\", \"message\":\"票据承兑成功！\", \"operationType\":\"add\"}");
        } catch (Exception e) {
            e.printStackTrace();
            renderText("{\"statusCode\":\"300\", \"message\":\"" + e.getMessage() + "\"}");
        }
    }
    //票据取消贴现
    @Action("pjgl_qxtx")
    public void pjglQxtx() {
        try {
            pj = pjglService.findPjDetail(pjId);
            if(checkPjSfdrjq(pj)){
                renderText("票据已经导入财务系统不许取消!");
            }else{
                pjglService.pjglQxtx(pj);
                renderText("票据已取消贴现!");
            }
        } catch (Exception e) {
            e.printStackTrace();
            renderText(e.getMessage());
        }
    }

    //票据取消托收
    @Action("pjgl_qxts")
    public void pjglQxts() {
        try {
            pj = pjglService.findPjDetail(pjId);
            if(checkPjSfdrjq(pj)){
                renderText("票据已经导入财务系统不许取消!");
            }else{
                pjglService.pjglQxts(pj);
                renderText("票据已取消托收!");
            }
        } catch (Exception e) {
            e.printStackTrace();
            renderText(e.getMessage());
        }
    }

    //票据取消承兑
    @Action("pjgl_qxcd")
    public void pjglQxcd() {
        try {
            pj = pjglService.findPjDetail(pjId);
            if(checkPjSfdrjq(pj)){
                renderText("票据已经导入财务系统不许取消!");
            }else{
                pjglService.pjglQxcd(pj);
                renderText("票据已取消承兑!");
            }
        } catch (Exception e) {
            e.printStackTrace();
            renderText(e.getMessage());
        }
    }
    //票据取消兑换
    @Action("pjgl_qxdh")
    public void pjglQxdh() {
        try {
            pj = pjglService.findPjDetail(pjId);
            if(checkPjSfdrjq(pj)){
                renderText("票据已经导入财务系统不许取消!");
            }else{
                pjglService.pjglQxdh(pj);
                renderText("票据已取消兑换!");
            }
        } catch (Exception e) {
            e.printStackTrace();
            renderText(e.getMessage());
        }
    }


    /**
     * 远程ajax验证
     */
    @Action("ajax_validate")
    public void ajaxValidate() {
        if (!this.pjglService.ajaxValidate(pjId,pjsflxType,validateVal,validateField))
            renderJson("{\"valid\":true}");
        else
            renderJson("{\"valid\":false}");
    }


    //企业机构导出
    @Action("pjgl_export")
    public String pjglExport() {
        try {
            SimpleDateFormat dateformat2 = new SimpleDateFormat("yyyy-MM-dd");
            if (pjStartDate != null)
                pjStartDate = convertDate(pjStartDate, true);
            if (pjEndDate != null)
                pjEndDate = convertDate(pjEndDate, false);
           List<Pj> pjList1 = pjglService.findAllPjglNoPage(pjhm,sfkId,pjsflxType,
                    pjlxType, khztType, gsId,
                   java.net.URLDecoder.decode(gsMc,"UTF-8"),
                   java.net.URLDecoder.decode(khhMc,"UTF-8"),pjStartDate,pjEndDate,zt,1, -1);
            //第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            //第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("票据汇总表");
            POIUtil poiUtil = new POIUtil(wb);
            //设置列宽
       if(pjsflxType==0){//应收票据
            sheet.setDefaultRowHeightInPoints(15);
            sheet.setDefaultColumnWidth((short) 14);
            sheet.setColumnWidth((short) 0, (short) (12 * 512));
            sheet.setColumnWidth((short) 1, (short) (12 * 512));
            sheet.setColumnWidth((short) 2, (short) (12 * 512));
            sheet.setColumnWidth((short) 3, (short) (12 * 512));
            sheet.setColumnWidth((short) 4, (short) (12 * 512));
            sheet.setColumnWidth((short) 5, (short) (12 * 512));
            sheet.setColumnWidth((short) 6, (short) (12 * 512));
            sheet.setColumnWidth((short) 7, (short) (12 * 512));
            sheet.setColumnWidth((short) 8, (short) (12 * 512));
            sheet.setColumnWidth((short) 9, (short) (12 * 512));
            sheet.setColumnWidth((short) 10, (short) (12 * 512));
            sheet.setColumnWidth((short) 11, (short) (12 * 512));
            sheet.setColumnWidth((short) 12, (short) (12 * 512));
            sheet.setColumnWidth((short) 13, (short) (12 * 512));
            //第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow((int) 0);
            row.setHeightInPoints(36);
            //第四步，创建单元格，并设置值表头  设置表头居中
            HSSFCellStyle titleStyle = poiUtil.createTitleHSSFCellStyle(wb);
            HSSFCell titleCell = row.createCell((short) 0);
            titleCell.setCellStyle(titleStyle);
            titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            titleCell.setCellValue("票据汇总表");
            sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 14));
            row = sheet.createRow((int) 1);
            row.setHeightInPoints(21);
            HSSFCell deptCell = row.createCell((short) 1);
            deptCell.setCellStyle(poiUtil.createMonthHSSFCellStyle(wb));
            deptCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            deptCell.setCellValue("导出日期:");
            HSSFCell dqrq = row.createCell((short) 2);
            dqrq.setCellStyle(poiUtil.createMonthHSSFCellStyle(wb));
            dqrq.setEncoding(HSSFCell.ENCODING_UTF_16);
            dqrq.setCellValue(dateformat2.format(new Date()));
            row = sheet.createRow(2);
            row.setHeightInPoints(25);
            //字段
            HSSFCell gsmc = row.createCell((short) 0);
            gsmc.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            gsmc.setEncoding(HSSFCell.ENCODING_UTF_16);
            gsmc.setCellValue("公司名称");
            HSSFCell pjlx = row.createCell((short) 1);
            pjlx.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            pjlx.setEncoding(HSSFCell.ENCODING_UTF_16);
            pjlx.setCellValue("票据类型");
            HSSFCell pjhm = row.createCell((short) 2);
            pjhm.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            pjhm.setEncoding(HSSFCell.ENCODING_UTF_16);
            pjhm.setCellValue("票据号码");
            HSSFCell skhm = row.createCell((short) 3);
            skhm.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            skhm.setEncoding(HSSFCell.ENCODING_UTF_16);
            skhm.setCellValue("收款号码");
            HSSFCell cpyh = row.createCell((short) 4);
            cpyh.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            cpyh.setEncoding(HSSFCell.ENCODING_UTF_16);
            cpyh.setCellValue("出票银行");
            HSSFCell cprq = row.createCell((short) 5);
            cprq.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            cprq.setEncoding(HSSFCell.ENCODING_UTF_16);
            cprq.setCellValue("出票日");
            HSSFCell dq = row.createCell((short) 6);
            dq.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            dq.setEncoding(HSSFCell.ENCODING_UTF_16);
            dq.setCellValue("到日期");
            HSSFCell dqje = row.createCell((short) 7);
            dqje.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            dqje.setEncoding(HSSFCell.ENCODING_UTF_16);
            dqje.setCellValue("到期总金额");
            HSSFCell cpr = row.createCell((short) 8);
            cpr.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            cpr.setEncoding(HSSFCell.ENCODING_UTF_16);
            cpr.setCellValue("出票人");
            HSSFCell skr = row.createCell((short) 9);
            skr.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            skr.setEncoding(HSSFCell.ENCODING_UTF_16);
            skr.setCellValue("收款人");
            HSSFCell jkdw = row.createCell((short) 10);
            jkdw.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            jkdw.setEncoding(HSSFCell.ENCODING_UTF_16);
            jkdw.setCellValue("交款单位");
            HSSFCell syrq = row.createCell((short) 11);
            syrq.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            syrq.setEncoding(HSSFCell.ENCODING_UTF_16);
            syrq.setCellValue("使用日期");
            HSSFCell bz = row.createCell((short) 12);
            bz.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
            bz.setEncoding(HSSFCell.ENCODING_UTF_16);
            bz.setCellValue("备注");
           HSSFCell zt = row.createCell((short) 13);
           zt.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           zt.setEncoding(HSSFCell.ENCODING_UTF_16);
           zt.setCellValue("状态");
            for (int i = 3; i < pjList1.size() + 3; i++) {
                row = sheet.createRow(i);
                row.setHeightInPoints(25);
                Pj p = pjList1.get(i - 3);
                gsmc = row.createCell((short) 0);
                gsmc.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                gsmc.setEncoding(HSSFCell.ENCODING_UTF_16);
                gsmc.setCellValue(p.getNbgs() == null ? "" : p.getNbgs().getQc());
                pjlx = row.createCell((short) 1);
                pjlx.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                pjlx.setEncoding(HSSFCell.ENCODING_UTF_16);
                pjlx.setCellValue(p.getPjlx() == 0 ? "银票" : "商票");
                pjhm = row.createCell((short) 2);
                pjhm.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                pjhm.setEncoding(HSSFCell.ENCODING_UTF_16);
                pjhm.setCellValue(p.getPjhm());
                skhm = row.createCell((short) 3);
                skhm.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                skhm.setEncoding(HSSFCell.ENCODING_UTF_16);
                skhm.setCellValue(p.getSk() == null ? "" : p.getSk().getBm());
                cpyh = row.createCell((short) 4);
                cpyh.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                cpyh.setEncoding(HSSFCell.ENCODING_UTF_16);
                cpyh.setCellValue(p.getYscpyh());
                cprq = row.createCell((short) 5);
                cprq.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                cprq.setEncoding(HSSFCell.ENCODING_UTF_16);
                cprq.setCellValue(p.getCprq() == null ? "" :dateformat2.format(p.getCprq()));
                dq = row.createCell((short) 6);
                dq.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                dq.setEncoding(HSSFCell.ENCODING_UTF_16);
                dq.setCellValue(p.getDqr() == null ? "" : dateformat2.format(p.getDqr()));
                dqje = row.createCell((short) 7);
                dqje.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                dqje.setEncoding(HSSFCell.ENCODING_UTF_16);
                dqje.setCellValue(CalcUtils.round(Double.parseDouble(p.getDqje().toString()), 2));
                cpr = row.createCell((short) 8);
                cpr.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                cpr.setEncoding(HSSFCell.ENCODING_UTF_16);
                cpr.setCellValue(p.getCpr());
                skr = row.createCell((short) 9);
                skr.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                skr.setEncoding(HSSFCell.ENCODING_UTF_16);
                skr.setCellValue(p.getSkr());
                jkdw = row.createCell((short) 10);
                jkdw.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                jkdw.setEncoding(HSSFCell.ENCODING_UTF_16);
                jkdw.setCellValue(p.getJpdw() == null ? "" : p.getJpdw().getQc());
                syrq = row.createCell((short) 11);
                syrq.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                syrq.setEncoding(HSSFCell.ENCODING_UTF_16);
                syrq.setCellValue(p.getSyrq() == null ? "" : p.getSyrq().toString());
                bz = row.createCell((short) 12);
                bz.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                bz.setEncoding(HSSFCell.ENCODING_UTF_16);
                bz.setCellValue(p.getBz());
                zt = row.createCell((short) 13);
                zt.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
                zt.setEncoding(HSSFCell.ENCODING_UTF_16);
                zt.setCellValue(checkPjStatus(p));
             }
       }else{ //应付票据
           sheet.setDefaultRowHeightInPoints(15);
           sheet.setDefaultColumnWidth((short) 14);
           sheet.setColumnWidth((short) 0, (short) (8 * 512));
           sheet.setColumnWidth((short) 1, (short) (8 * 512));
           sheet.setColumnWidth((short) 2, (short) (8 * 512));
           sheet.setColumnWidth((short) 3, (short) (8 * 512));
           sheet.setColumnWidth((short) 4, (short) (8 * 512));
           sheet.setColumnWidth((short) 5, (short) (8 * 512));
           sheet.setColumnWidth((short) 6, (short) (8 * 512));
           sheet.setColumnWidth((short) 7, (short) (8 * 512));
           sheet.setColumnWidth((short) 8, (short) (8 * 512));
           sheet.setColumnWidth((short) 9, (short) (8 * 512));
           sheet.setColumnWidth((short) 10, (short) (8 * 512));
           sheet.setColumnWidth((short) 11, (short) (8 * 512));
           //第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
           HSSFRow row = sheet.createRow((int) 0);
           row.setHeightInPoints(36);
           //第四步，创建单元格，并设置值表头  设置表头居中
           HSSFCellStyle titleStyle = poiUtil.createTitleHSSFCellStyle(wb);
           HSSFCell titleCell = row.createCell((short) 0);
           titleCell.setCellStyle(titleStyle);
           titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
           titleCell.setCellValue("票据汇总表");
           sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 11));
           row = sheet.createRow((int) 1);
           row.setHeightInPoints(21);
           HSSFCell deptCell = row.createCell((short) 1);
           deptCell.setCellStyle(poiUtil.createMonthHSSFCellStyle(wb));
           deptCell.setEncoding(HSSFCell.ENCODING_UTF_16);
           deptCell.setCellValue("导出日期:");
           HSSFCell dqrq = row.createCell((short) 2);
           dqrq.setCellStyle(poiUtil.createMonthHSSFCellStyle(wb));
           dqrq.setEncoding(HSSFCell.ENCODING_UTF_16);
           dqrq.setCellValue(dateformat2.format(new Date()));
           row = sheet.createRow(2);
           row.setHeightInPoints(25);
           //字段
           HSSFCell gsmc = row.createCell((short) 0);
           gsmc.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           gsmc.setEncoding(HSSFCell.ENCODING_UTF_16);
           gsmc.setCellValue("公司名称");
           HSSFCell pjlx = row.createCell((short) 1);
           pjlx.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           pjlx.setEncoding(HSSFCell.ENCODING_UTF_16);
           pjlx.setCellValue("票据类型");
           HSSFCell pjhm = row.createCell((short) 2);
           pjhm.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           pjhm.setEncoding(HSSFCell.ENCODING_UTF_16);
           pjhm.setCellValue("票据号码");
           HSSFCell fkhm = row.createCell((short) 3);
           fkhm.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           fkhm.setEncoding(HSSFCell.ENCODING_UTF_16);
           fkhm.setCellValue("付款号码");
           HSSFCell cpyh = row.createCell((short) 4);
           cpyh.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           cpyh.setEncoding(HSSFCell.ENCODING_UTF_16);
           cpyh.setCellValue("出票银行");
           HSSFCell cprq = row.createCell((short) 5);
           cprq.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           cprq.setEncoding(HSSFCell.ENCODING_UTF_16);
           cprq.setCellValue("出票日");
           HSSFCell dq = row.createCell((short) 6);
           dq.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           dq.setEncoding(HSSFCell.ENCODING_UTF_16);
           dq.setCellValue("到日期");
           HSSFCell dqje = row.createCell((short) 7);
           dqje.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           dqje.setEncoding(HSSFCell.ENCODING_UTF_16);
           dqje.setCellValue("到期总金额");
           HSSFCell spr = row.createCell((short) 8);
           spr.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           spr.setEncoding(HSSFCell.ENCODING_UTF_16);
           spr.setCellValue("收票人");
           HSSFCell syrq = row.createCell((short) 9);
           syrq.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           syrq.setEncoding(HSSFCell.ENCODING_UTF_16);
           syrq.setCellValue("使用日期");
           HSSFCell bz = row.createCell((short) 10);
           bz.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           bz.setEncoding(HSSFCell.ENCODING_UTF_16);
           bz.setCellValue("备注");
           HSSFCell zt = row.createCell((short) 11);
           zt.setCellStyle(poiUtil.createHeaderHSSFCellStyle(wb));
           zt.setEncoding(HSSFCell.ENCODING_UTF_16);
           zt.setCellValue("状态");
           for (int i = 3; i < pjList1.size() + 3; i++) {
               row = sheet.createRow(i);
               row.setHeightInPoints(25);
               Pj p = pjList1.get(i - 3);
               gsmc = row.createCell((short) 0);
               gsmc.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               gsmc.setEncoding(HSSFCell.ENCODING_UTF_16);
               gsmc.setCellValue(p.getNbgs() == null ? "" : p.getNbgs().getQc());
               pjlx = row.createCell((short) 1);
               pjlx.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               pjlx.setEncoding(HSSFCell.ENCODING_UTF_16);
               pjlx.setCellValue(p.getPjlx() == 0 ? "银票" : "商票");
               pjhm = row.createCell((short) 2);
               pjhm.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               pjhm.setEncoding(HSSFCell.ENCODING_UTF_16);
               pjhm.setCellValue(p.getPjhm());
               fkhm = row.createCell((short) 3);
               fkhm.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               fkhm.setEncoding(HSSFCell.ENCODING_UTF_16);
               fkhm.setCellValue(p.getSk() == null ? "" : p.getSk().getBm());
               cpyh = row.createCell((short) 4);
               cpyh.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               cpyh.setEncoding(HSSFCell.ENCODING_UTF_16);
               cpyh.setCellValue(p.getYfcpyh()==null?"":p.getYfcpyh().getQc());
               cprq = row.createCell((short) 5);
               cprq.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               cprq.setEncoding(HSSFCell.ENCODING_UTF_16);
               cprq.setCellValue(p.getCprq() == null ? "" : dateformat2.format(p.getCprq()));
               dq = row.createCell((short) 6);
               dq.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               dq.setEncoding(HSSFCell.ENCODING_UTF_16);
               dq.setCellValue(p.getDqr() == null ? "" :dateformat2.format(p.getDqr()));
               dqje = row.createCell((short) 7);
               dqje.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               dqje.setEncoding(HSSFCell.ENCODING_UTF_16);
               dqje.setCellValue(CalcUtils.round(Double.parseDouble(p.getDqje().toString()), 2));
               spr = row.createCell((short) 8);
               spr.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               spr.setEncoding(HSSFCell.ENCODING_UTF_16);
               spr.setCellValue(p.getSpr()==null?"":p.getSpr().getQc());
               syrq = row.createCell((short) 9);
               syrq.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               syrq.setEncoding(HSSFCell.ENCODING_UTF_16);
               syrq.setCellValue(p.getSyrq() == null ? "" : p.getSyrq().toString());
               bz = row.createCell((short) 10);
               bz.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               bz.setEncoding(HSSFCell.ENCODING_UTF_16);
               bz.setCellValue(p.getBz());
               zt = row.createCell((short) 11);
               zt.setCellStyle(poiUtil.createContentHSSFCellStyle(wb));
               zt.setEncoding(HSSFCell.ENCODING_UTF_16);
               zt.setCellValue(checkPjStatus(p));
           }
       }
            response.setContentType("application/x-download");
            String filedisplay = new String(("票据汇总表.xls").getBytes("gb2312"), "iso8859-1");
            response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
            renderText("发生异常，导出失败" + e.getMessage());
        }
        return SUCCESS;
    }


    /**
     * @param date
     * @param flag true:当天00:00:00   false:第二天00:00:00
     * @return
     */
    private Date convertDate(Date date, boolean flag) {
        GregorianCalendar gc = new GregorianCalendar();
        gc.setTime(date);
        if (!flag)
            gc.add(GregorianCalendar.DAY_OF_MONTH, 1);
        gc.set(GregorianCalendar.HOUR, 0);
        gc.set(GregorianCalendar.MINUTE, 0);
        gc.set(GregorianCalendar.SECOND, 0);
        return gc.getTime();
    }

    //判断票据是否已经导入jiuqi 导入返回true 没有返回false
    public Boolean  checkPjSfdrjq(Pj pj){
        if(pj.getInsertFlag()==3){
            return true;
        }else{
            return false;
        }
    }

    //判断票据是否已经导入jiuqi 导入返回true 没有返回false
    private String  checkPjStatus(Pj pj){
        String zt = "";
      if(pj.getPjsflx()==0 && pj.getStatus()==1){//应收
          zt= "可操作";
      }
      if(pj.getPjsflx()==0 && pj.getStatus()==5){//应收
          zt= "已背书";
      }
      if(pj.getPjsflx()==0 && pj.getStatus()==2){//应收
          zt= "已贴现";
      }
      if(pj.getPjsflx()==0 && pj.getStatus()==3){//应收
          zt= "已托收";
      }
      if(pj.getPjsflx()==0 && pj.getStatus()==6){//应收
          zt= "已兑换";
      }
      if(pj.getPjsflx()==1 && pj.getStatus()==1){//应收
            zt= "可操作";
      }
      if(pj.getPjsflx()==1 && pj.getStatus()==4){//应付
            zt= "已承兑";
      }
      if(pj.getPjsflx()==1 && pj.getStatus()==5){//应付
            zt= "已使用";
      }
      return zt;
    }



    //=============================GetterandSetter============================================


    public Pj getPj() {
        return pj;
    }

    public void setPj(Pj pj) {
        this.pj = pj;
    }

    public Long getPjId() {
        return pjId;
    }

    public void setPjId(Long pjId) {
        this.pjId = pjId;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
    }

    public String getValidateVal() {
        return validateVal;
    }

    public void setValidateVal(String validateVal) {
        this.validateVal = validateVal;
    }

    public String getValidateField() {
        return validateField;
    }

    public void setValidateField(String validateField) {
        this.validateField = validateField;
    }

    public Integer getPjsflxType() {
        return pjsflxType;
    }

    public void setPjsflxType(Integer pjsflxType) {
        this.pjsflxType = pjsflxType;
    }

    public Integer getPjlxType() {
        return pjlxType;
    }

    public void setPjlxType(Integer pjlxType) {
        this.pjlxType = pjlxType;
    }

    public Integer getKhztType() {
        return khztType;
    }

    public void setKhztType(Integer khztType) {
        this.khztType = khztType;
    }

    public Long getGsId() {
        return gsId;
    }

    public void setGsId(Long gsId) {
        this.gsId = gsId;
    }

    public String getGsMc() {
        return gsMc;
    }

    public void setGsMc(String gsMc) {
        this.gsMc = gsMc;
    }

    public String getPjIds() {
        return pjIds;
    }

    public void setPjIds(String pjIds) {
        this.pjIds = pjIds;
    }

    public List<Pj> getPjList() {
        return pjList;
    }

    public void setPjList(List<Pj> pjList) {
        this.pjList = pjList;
    }

    public Long getYhmcId() {
        return yhmcId;
    }

    public void setYhmcId(Long yhmcId) {
        this.yhmcId = yhmcId;
    }

    public Long getGsmcId() {
        return gsmcId;
    }

    public void setGsmcId(Long gsmcId) {
        this.gsmcId = gsmcId;
    }

    public Long getBmmcId() {
        return bmmcId;
    }

    public void setBmmcId(Long bmmcId) {
        this.bmmcId = bmmcId;
    }

    public Long getSqrId() {
        return sqrId;
    }

    public void setSqrId(Long sqrId) {
        this.sqrId = sqrId;
    }

    public Sjzdz getLxfylb() {
        return lxfylb;
    }

    public void setLxfylb(Sjzdz lxfylb) {
        this.lxfylb = lxfylb;
    }

    public String getTxlxzc() {
        return txlxzc;
    }

    public void setTxlxzc(String txlxzc) {
        this.txlxzc = txlxzc;
    }

    public String getYhmc() {
        return yhmc;
    }

    public void setYhmc(String yhmc) {
        this.yhmc = yhmc;
    }

    public String getGsmc() {
        return gsmc;
    }

    public void setGsmc(String gsmc) {
        this.gsmc = gsmc;
    }

    public Long getKhhId() {
        return khhId;
    }

    public void setKhhId(Long khhId) {
        this.khhId = khhId;
    }

    public String getKhhMc() {
        return khhMc;
    }

    public void setKhhMc(String khhMc) {
        this.khhMc = khhMc;
    }

    public Long getSfkId() {
        return sfkId;
    }

    public void setSfkId(Long sfkId) {
        this.sfkId = sfkId;
    }

    public List<Gncz> getGnczList() {
        return gnczList;
    }

    public void setGnczList(List<Gncz> gnczList) {
        this.gnczList = gnczList;
    }

    public Date getPjStartDate() {
        return pjStartDate;
    }

    public void setPjStartDate(Date pjStartDate) {
        this.pjStartDate = pjStartDate;
    }

    public Date getPjEndDate() {
        return pjEndDate;
    }

    public void setPjEndDate(Date pjEndDate) {
        this.pjEndDate = pjEndDate;
    }

    public Pj getPjdh() {
        return pjdh;
    }

    public void setPjdh(Pj pjdh) {
        this.pjdh = pjdh;
    }

    public String getCdlxzc() {
        return cdlxzc;
    }

    public void setCdlxzc(String cdlxzc) {
        this.cdlxzc = cdlxzc;
    }

    public String getPjhm() {
        return pjhm;
    }

    public void setPjhm(String pjhm) {
        this.pjhm = pjhm;
    }

    public String getSysDate() {
        return sysDate;
    }

    public void setSysDate(String sysDate) {
        this.sysDate = sysDate;
    }

    public Integer getZt() {
        return zt;
    }

    public void setZt(Integer zt) {
        this.zt = zt;
    }

    public String getBz() {
        return bz;
    }

    public void setBz(String bz) {
        this.bz = bz;
    }

    @Autowired
    public void setPjglService(PjglService pjglService) {
        this.pjglService = pjglService;
    }
    @Autowired
    public void setKhhService(KhhService khhService) {
        this.khhService = khhService;
    }
    @Autowired
    public void setNbgsService(NbgsService nbgsService) {
        this.nbgsService = nbgsService;
    }
    @Autowired
    public void setGnczService(GnczService gnczService) {
        this.gnczService = gnczService;
    }
}
